USE [AnotherSuccessfulProcedure]

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[PlayerClassStatsSelectByPlayerIdLogId]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
	DROP PROCEDURE dbo.PlayerClassStatsSelectByPlayerIdLogId
END


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Neil Wilson
-- Create date: 11/03/08
-- Description:	PlayerClassStats Table Select By PlayerId and LogId
-- =============================================
CREATE PROCEDURE PlayerClassStatsSelectByPlayerIdLogId

			@PlayerId varchar(30),
			@ClassId int,
			@LogId varchar(8000)
	
AS
BEGIN

DECLARE 
		@ReturnName varchar(50),
		@Points varchar(8),
		@Kills int,
		@Assists int,
		@Deaths int,
		@KillDeathRatio varchar(8),
		@Dominations int,
		@Ubers int,
		@Healing float,
		@TimesPlayed int
		
		


SELECT @ReturnName = ClassLongName From classes
WHERE ClassId = @ClassId



DECLARE @SQL varchar(600)


DECLARE @ResultSet TABLE (resultCount int)

-- KILLS
SET @SQL = 'SELECT COUNT(KillId) FROM KILLS WHERE Attacker_PlayerId = ''' + @PlayerId + '''
				AND Attacker_ClassId = ' + CAST(@ClassId AS varchar(2)) + '
				AND LogId IN (' + @LogId + ')'

DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
SELECT @Kills = resultCount FROM @ResultSet

-- ASSISTS
SET @SQL = 'SELECT COUNT(AssistId) FROM Assists WHERE assister_PlayerId = ''' + @PlayerId + '''
				AND ClassId = ' + CAST(@ClassId AS varchar(2)) + '
				AND LogId IN (' + @LogId + ')'

DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
SELECT @Assists= resultCount FROM @ResultSet

-- DEATHS
SET @SQL = 'SELECT COUNT(KillId) FROM KILLS WHERE Victim_PlayerId = ''' + @PlayerId + '''
				AND Victim_classId = ' + CAST(@ClassId AS varchar(2)) + '
				AND LogId IN (' + @LogId + ')'

DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
SELECT @Deaths= resultCount FROM @ResultSet


-- UBERS 
	
SET @SQL = 'SELECT COUNT(ChargeId) FROM MedicCharge WHERE PlayerId = ''' + @PlayerId + '''
				AND LogId IN (' + @LogId + ')'

DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
SELECT @Ubers= resultCount FROM @ResultSet

-- DOMINATIONS

SET @SQL = 'SELECT Count(DominationId) FROM Dominations WHERE Attacker_playerId = ''' + @PlayerId + '''
				AND Attacker_classId = ' + CAST(@ClassId AS varchar(2)) + '
				AND LogId IN (' + @LogId + ')'


DELETE FROM @ResultSet
INSERT INTO @ResultSet EXEC(@SQL)
SELECT @Dominations = resultCount FROM @ResultSet


SET @Healing = 0.0

SET @TimesPlayed = 0

select @ReturnName as [ClassName], 0 as [Points], 
		@TimesPlayed as [TimesPlayed],
		@Kills as [Kills], @Assists as [Assists], 
		@Deaths as [Deaths], 0.0 as [KillDeathRatio],
		@Dominations as [Dominations], @Ubers as [Ubers],
		@PlayerId as [PlayerId], @ClassId as [ClassId]

END
GO

--    PlayerClassStatsSelectByPlayerIdLogId 'STEAM_0:0:13191839', 7, '1,3'



